package com.gopay.common.rcs.dao.antiphish.impl;

import java.io.Serializable;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.gopay.common.base.dao.impl.BaseDAOImpl;
import com.gopay.common.cipher.utils.InUtils;
import com.gopay.common.cipher.utils.SensitiveInfoUtils;
import com.gopay.common.constants.rcs.AntiPhishCheckOrderIpEnum;
import com.gopay.common.constants.rcs.AntiPhishGetwayChannelEnum;
import com.gopay.common.constants.trans.GatewayChannel;
import com.gopay.common.domain.rcs.RcsAntiPhishConfCust;
import com.gopay.common.rcs.bean.AntiPhishType;
import com.gopay.common.rcs.dao.antiphish.RcsAntiPhishingCustDAO;
import com.gopay.common.rcs.vo.RcsAntiPhishConfCustlVo;
import com.gopay.common.util.StringTools;

/**
 * 防钓鱼商户配置
 * 
 * @RcsAntiPhishingCustDAO.java
 * @author liujy
 * @2013-7-23 下午6:40:27 www.gopay.com.cn Inc.All rights reserved.
 */
@Repository("rcsAntiPhishingCustDAO")
public class RcsAntiPhishingCustDAOImpl extends BaseDAOImpl<RcsAntiPhishConfCust, Serializable> implements
        RcsAntiPhishingCustDAO {
	
    //private final Logger logger = LoggerFactory.getLogger(RcsAntiPhishingCustDAOImpl.class);

    /*
     * 查询商户的钓鱼规则列表
     * 
     * @see
     * com.gopay.common.rcs.dao.antiphish.RcsAntiPhishingCustDAO#getCustConfBySql
     * (java.lang.String, java.lang.String, int, int)
     */
    @Override
    public List<?> getCustConfListBySql(String custName, String email, String custMob, final int pageNo, final int pageSize) {

        final String sql = getConfListSql(custName, email, custMob);

        return findBySql(sql, pageNo, pageSize);
    }

    private String getConfListSql(String custName, String email, String custMob) {
        String sql = " select "
                + "     r.ANTI_PHISH_TYPE,              "// 0
                + "     r.IS_ENABLED,                   " // 1
                + "     r.LEGAL_VALUE,                  " // 2
                + "     r.ILLEGAL_REQ_DICISION,         " // 3
                + "     cast(r.GATEWAY_CHANNEL  as varchar2(2)) GATEWAY_CHANNEL, "// 4
                + "     c.email,                        "// 5
                + "     c.cust_mob,                     "// 6
                + "     c.corp_name,                    "// 7
                + "     c.MER_NAME ,                    "// 8
                + "     c.cust_id ,                      "// 9
                + "     r.conf_sq                       "// 10
                + " from  RCS_ANTI_PHISH_CONF_CUST r    " + " left join cust_corp_info c          "
                + "     on c.cust_id = r.cust_id        " + " where c.IS_MERCHANT=1 "
                + buildWhereSql(custName, email, custMob, "c.") + "  order by c.AUDIT_TIME desc ";
        return sql;
    }

    /**
     * 构建查询条件
     * 
     * @param custName
     * @param email
     * @return
     */
    private String buildWhereSql(String custName, String email, String custMob, String subNo) {
        StringBuffer andSQL = new StringBuffer();
        if (!StringTools.checkNulltoString(custName).equals("")) {
            andSQL.append("and ").append(subNo).append("corp_name like '%").append(custName).append("%'");
        }
        if (!StringTools.checkNulltoString(email).equals("")) {
            andSQL.append("and ").append(subNo).append("email like'%").append(email).append("%'");
        }
        if (!StringTools.checkNulltoString(custMob).equals("")) {
//            andSQL.append("and ").append(subNo).append("cust_mob ='").append(custMob).append("'");
        	
        	  /** 央行检测 修改手机号  modify by ssj 2017-04-11  加密  考虑到后期可能会有多个秘钥体系采用in进行查询*/
            andSQL.append("and ").append(subNo).append("cust_mob in ").append(InUtils.getListIn(custMob));
            /**end*/
           
        }
        return andSQL.length() > 0 ? andSQL.toString() : "";
    }

    /*
     * 查询总数
     * 
     * @see
     * com.gopay.common.rcs.dao.antiphish.RcsAntiPhishingCustDAO#getCount(java
     * .lang.String, java.lang.String)
     */
    @Override
    public int getCount(String custName, String email, String custMob) {
        StringBuffer countSQL = new StringBuffer();
        countSQL.append(
                "select count(*) from   RCS_ANTI_PHISH_CONF_CUST r left join cust_corp_info c   on c.cust_id = r.cust_id where c.IS_MERCHANT=1 ")
                .append(buildWhereSql(custName, email, custMob, "c."));
        return findCountBySql(countSQL.toString());
    }

    /*
     * 更新验证规则
     * 
     * @see
     * com.gopay.common.rcs.dao.antiphish.RcsAntiPhishingCustDAO#updateCustConf
     * (com.gopay.common.vo.rcs.antiphish.RcsAntiPhishConfCust)
     */
    @Override
    public void updateCustConf(RcsAntiPhishConfCust custConf) {
        String hql = buidUpdateSql(custConf);
        updateByHql(hql);
    }

    /**
     * 构建更新sql
     * 
     * @param custConf
     * @return
     */
    private String buidUpdateSql(RcsAntiPhishConfCust custConf) {
        StringBuffer hql = new StringBuffer("update RcsAntiPhishConfCust set conf_sq=").append(custConf.getConfSq())
                .append(getSetSql(custConf)).append(" where conf_sq=").append(custConf.getConfSq());
        return hql.toString();
    }

    /**
     * 更新条件
     * 
     * @param custConf
     * @return
     */
    private String getSetSql(RcsAntiPhishConfCust custConf) {
        StringBuffer setSql = new StringBuffer();
        // 开关是否更新
        if (custConf.getIsEnabled() != -1) {
            setSql.append(" ,IS_ENABLED=").append(custConf.getIsEnabled());
        }
        // 订单ip校验 处理方式 变化
        if (custConf.getIllegalReqDicision() != null) {
            setSql.append(" ,ILLEGAL_REQ_DICISION='").append(custConf.getIllegalReqDicision()).append("'");
        }
        // 合法值变化
        if (custConf.getLegalValue() != null) {
            setSql.append(" ,LEGAL_VALUE='").append(custConf.getLegalValue()).append("'");
        }
        // 网关通道发送变化
        if (custConf.getGatewayChannel() != null) {
            setSql.append(" ,GATEWAY_CHANNEL='").append(custConf.getGatewayChannel()).append("'");
        }
        return setSql.toString();
    }

    /*
     * 查询指定商户的钓鱼规则
     * 
     * @see
     * com.gopay.common.rcs.dao.antiphish.RcsAntiPhishingCustDAO#getCustConfBySql
     * (java.lang.String, java.lang.String, int, int)
     */
    @Override
    public List<?> getCustConfBySql(String custId) {
        final String sql = getBaseSql(custId);
        return findBySql(sql);
    }

    /**
     * 基础查询指定商户设置信息sql
     * 
     * @param email
     * @return
     */
    private String getBaseSql(String custId) {
        final String sql = " select  "
                + "     r.ANTI_PHISH_TYPE,              "// 0
                + "     r.IS_ENABLED,                   " // 1
                + "     r.LEGAL_VALUE,                  " // 2
                + "     r.ILLEGAL_REQ_DICISION,         " // 3
                + "     cast(r.GATEWAY_CHANNEL  as varchar2(2)) GATEWAY_CHANNEL,                          "// 4
                + "     c.email,                        "// 5
                + "     c.cust_mob,                        "// 6
                + "     c.corp_name,                    "// 7
                + "     c.MER_NAME ,                     "// 8
                + "     c.cust_id ,                     " // 9
                + "     r.conf_sq                      " // 10
                + " from   RCS_ANTI_PHISH_CONF_CUST r      " + " left join   CUST_CORP_INFO c "
                + "     on c.cust_id = r.cust_id            " + " where c.IS_MERCHANT=1   "
                + "  and   c.cust_id='"+custId+"'";
        return sql;
    }

    /*
     * 规则未初始化是 只查询商户信息
     * 
     * @see
     * com.gopay.common.rcs.dao.antiphish.RcsAntiPhishingCustDAO#getMerchantBySql
     * (java.lang.String, java.lang.String, int, int)
     */
    @Override
    public List<RcsAntiPhishConfCustlVo> getMerchantBySql(String custName, String email, String custMob, final int pageNo,
            final int pageSize) {

        final String sql = getMerchantSql(custName, email, custMob);
        
        List<?> objList = findBySql(sql, pageNo, pageSize);
        if (objList.size() > 0) {
            List<RcsAntiPhishConfCustlVo> confList = new ArrayList<RcsAntiPhishConfCustlVo>();
            return buidConfList(confList, objList);
        }
        return null;
    }

    private List<RcsAntiPhishConfCustlVo> buidConfList(List<RcsAntiPhishConfCustlVo> confList, List<?> objList) {
        for (int i = 0, size = null == objList ? 0 : objList.size(); i < size; i++) {
        	
        	Object[] objArray = (Object[])objList.get(i);
        	
            List<RcsAntiPhishConfCust> custConfList=getCustConfListByCustId((String)objArray[4]);
            if(custConfList!=null&&custConfList.size()!=0){
                RcsAntiPhishConfCustlVo conf = new RcsAntiPhishConfCustlVo();
                for(RcsAntiPhishConfCust custconf: custConfList){
                    if(custconf.getAntiPhishType().equals("01")){
                            conf.setCheckDomain(custconf.getIsEnabled()+"");
                            conf.setDomainId(custconf.getConfSq()+"");
                      }else if(custconf.getAntiPhishType().equals("02")){
                            conf.setCheckLikeDomain(custconf.getIsEnabled()+"");
                            conf.setCheckLikeDomainId(custconf.getConfSq()+"");
                      }else if(custconf.getAntiPhishType().equals("03")){
                          conf.setLegalValue("45");// 默认45秒不可更改
                          conf.setCheckTimeStamp(custconf.getIsEnabled()+"");
                          conf.setTimeStampId(custconf.getConfSq()+"");
                      }else if(custconf.getAntiPhishType().equals("04")){
                          if(custconf.getIsEnabled()==0){
                              conf.setCheckOrderIP("00");
                          }else{
                              conf.setCheckOrderIP(custconf.getIllegalReqDicision());
                          }
                          conf.setOrderIpId(custconf.getConfSq()+"");
                      }else if(custconf.getAntiPhishType().equals("05")){
                          if(custconf.getIsEnabled()==0){
                              conf.setEnterValidityCode("03");
                          }else{
                              conf.setEnterValidityCode(custconf.getGatewayChannel());
                          }
                          conf.setEnterValidatiCodeId(custconf.getConfSq()+"");
                      }else if(custconf.getAntiPhishType().equals("06")){
                          conf.setReqSourceIp(custconf.getLegalValue());
                          conf.setReqSourceIpId(custconf.getConfSq()+"");
                      }
                }
                conf.setCustEmail((String) objArray[0]);
                conf.setCustMob((String) objArray[1]);
                conf.setCustName((String) objArray[2]);
                conf.setSiteName((String) objArray[3]);
                conf.setCustId((String) objArray[4]);
                confList.add(conf);
                
            }else{
                RcsAntiPhishConfCustlVo conf = new RcsAntiPhishConfCustlVo();
                conf.setCheckDomain("0");
                conf.setCheckLikeDomain("0");
                conf.setCheckOrderIP(AntiPhishCheckOrderIpEnum.CLAOSE.value);
                conf.setCheckTimeStamp("0");
                conf.setCustEmail((String) objArray[0]);
                conf.setCustMob((String) objArray[1]);
                conf.setCustId((String) objArray[4]);
                conf.setCustName((String) objArray[2]);
                conf.setEnterValidityCode(AntiPhishGetwayChannelEnum.ALL_CLOSE.value);
                conf.setSiteName((String) objArray[3]);
                confList.add(conf);
                conf.setLegalValue("45");// 默认45秒不可更改
                // 构建只有商户信息，规则信息都为关闭的对象
            }
        }
        return confList;
    }
    private List<RcsAntiPhishConfCust> getCustConfListByCustId(String custId){
        Map<String, Object>  param=new HashMap<String, Object>();
        param.put("custId", custId);
        String hql="from RcsAntiPhishConfCust where custId =:custId";
        return findByHql(hql, param);
        
    }

    private String getMerchantSql(String custName, String email, String custMob) {
        String sql = " select "
                + "     c.email,                        "// 0
                + "     c.cust_mob,                     "// 1
                + "     c.corp_name,                    "// 2
                + "     c.MER_NAME ,                    "// 3
                + "     c.cust_id                       "// 4
                + " from  cust_corp_info c    " + " where c.IS_MERCHANT=1 " + buildWhereSql(custName, email, custMob, "c.")
                + "  order by c.AUDIT_TIME desc ";
        return sql;

    }
    
    @Override
    public int getMerchantCount(String custName, String custEmail, String custMob) {
        String sql = " select count(*)   " + " from  cust_corp_info c    " + " where c.IS_MERCHANT=1 "
                + buildWhereSql(custName, custEmail, custMob, "c.");
        return findCountBySql(sql);
    }

	@Override
	public RcsAntiPhishConfCustlVo getMerchantByCustId(String custId) {
		 final String sql = " select "
	                + "     c.email,                        "// 0
	                + "     c.cust_mob,                     "// 1
	                + "     c.corp_name,                    "// 2
	                + "     c.MER_NAME ,                    "// 3
	                + "     c.cust_id                       "// 4
	                + " from  cust_corp_info c     where cust_id =:custId" ;
	        
		 Map<String, Object>  param=new HashMap<String, Object>();
	     param.put("custId", custId);
	     List<?> objList = findBySql(sql,param);
	        if (objList.size() == 1) {
	            List<RcsAntiPhishConfCustlVo> confList = new ArrayList<RcsAntiPhishConfCustlVo>();
	            List<RcsAntiPhishConfCustlVo> confList_tmp = buidConfList(confList, objList); 
	            return confList_tmp.size() >0 ? confList_tmp.get(0):null ;
	        }
	        return null;
	}
    
    @Override
    public String getLegalValue(final String custId, final AntiPhishType type, final GatewayChannel gch) {
        final StringBuilder buf = new StringBuilder(512);
        buf.append(" SELECT  M.LEGAL_VALUE VALUE");
        buf.append("  FROM RCS_ANTI_PHISH_CONF_CUST M");
        buf.append(" WHERE M.CUST_ID = '" + custId + "'");
        buf.append("   AND M.ANTI_PHISH_TYPE = '" + type.code + "'");
        if (null != gch) {
            buf.append("   AND M.GATEWAY_CHANNEL = '" + gch.value + "'");
        }
        
        List<?> list = findBySql(buf.toString());
        if(null == list || list.isEmpty()){
        	return null;
        }
        return (String)list.get(0);
    }

    @Override
    public RcsAntiPhishConfCust getByCustId(String custId) {
        String hql="from RcsAntiPhishConfCust where custId='"+custId+"' and antiPhishType='05' ";
        List<RcsAntiPhishConfCust> list=findByHql(hql);
          return list.size()>0?list.get(0):null;
    }
    @Override
    public void delLogo(String merchantId) {
       String hql="UPDATE RcsAntiPhishConfCust set legalValue='' where custId='"+merchantId+"' and antiPhishType='05'";
       updateByHql(hql);
    }
    
}
